﻿SELECT
	[main].object_id AS [ObjectId],
	[main].[name] AS [ObjectName],
	[main].[uses_ansi_nulls] AS [UsesAnsiNulls],
	[schema].[name] AS [SchemaName],
	[cols].[name] AS [ColumnName],
	[cols].[column_id] AS [ColumnId],

	-- Type information
	[systypes].[name] AS [TypeName],
	[cols].[max_length] AS [MaxLength],
	[cols].[precision] AS [Precision],
	[cols].[scale] AS [Scale],
	[cols].[is_nullable] AS [IsNullable],

	CASE [cols].[default_object_id] WHEN 0 THEN 0 ELSE 1 END AS [HasDefault],
	object_definition([cols].[default_object_id]) AS [DefaultValue],
	[cols].[collation_name] AS [Collation],
	[cols].[is_ansi_padded] AS [IsAnsiPadded],
	[cols].[is_rowguidcol] AS [IsRowGuid],

	-- Identity Columns
	[cols].[is_identity] AS [IsIdentity],
	[idcols].[seed_value] AS [IdentitySeed],
	[idcols].[increment_value] AS [IdentityIncrement],

	-- Computed columns
	[cols].[is_computed] AS [IsComputed],
	[cmpcols].[definition] AS [ComputationDefinition],
	[cmpcols].[uses_database_collation] AS [ComputationUsesDatabaseCollation],
	[cmpcols].[is_persisted] AS [IsComputationPersisted],
	
	-- PK and UQ Constraints
	[keycons].[type] AS [KeyConstraintType],
	[keycons].[is_system_named] AS [IsKeyConstraintSystemNamed],
	
	-- Full text data
	CASE [ftcols].object_id WHEN [main].object_id THEN 1 ELSE 0 END AS [IsFullTextIndexed]

FROM sys.tables [main]
INNER JOIN sys.schemas [schema] ON [main].[schema_id] = [schema].[schema_id]

-- Get normal columns
LEFT OUTER JOIN sys.columns [cols] ON [main].object_id = [cols].object_id
LEFT OUTER JOIN sys.systypes [systypes] ON [cols].[system_type_id] = [systypes].[xtype]

-- Get identity columns
LEFT OUTER JOIN sys.identity_columns [idcols] ON [main].object_id = [idcols].object_id AND [cols].[column_id] = [idcols].[column_id]

-- Get computed columns
LEFT OUTER JOIN sys.computed_columns [cmpcols] ON [main].object_id = [cmpcols].object_id AND [cols].[column_id] = [cmpcols].[column_id]

--Get PK and UQ Constraints
LEFT OUTER JOIN sys.index_columns [ixcols] ON [main].object_id = [ixcols].object_id AND [cols].[column_id] = [ixcols].[column_id]
LEFT OUTER JOIN sys.key_constraints [keycons] ON [ixcols].object_id = [keycons].[parent_object_id] AND [ixcols].[index_id] = [keycons].[unique_index_id]

-- Get Full text columns
LEFT OUTER JOIN sys.fulltext_index_columns [ftcols] ON [main].object_id = [ftcols].object_id AND [cols].[column_id] = [ftcols].[column_id]

WHERE
	[systypes].[name] != 'sysname'
-- [WHERECLAUSE]

ORDER BY
	[main].[name],
	[cols].[column_id]
